import fs from 'fs';
import path from 'path';
import { connection } from '../app/database/mysql';
import { categoryModal } from './category.model';
import { APP_URL, STATIC_PATH } from '../app/app.config';
import { cornerStatusChangeByType, saveCorner } from '../corner/common.service';
import { computeCategory } from '../kitap/kitap.service';

/**
 * 获取内容
 */
export interface GetHouseOptionsPagination {
  limit?: number;
  offset?: number;
}

export const getCategory = async (status: number) => {
  // 准备查询
  let statement = `
    SELECT * FROM categorys
  `;
  let where = []
  if (status >= 0) {
    where.push(`status = ` + status)
  }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  }
  statement += ` ORDER by id ASC `

  // 执行查询
  const [data] = await connection.promise().query(statement);

  // 提供数据
  return data;
}

/**
 * 按 ID 获取内容
 */
export const getIdCategory = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM categorys
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);
  let house = data[0] as any
  if (house) {
    let images = await findFileById(0, house.id, 3);
    house.images = images;
  }

  // 提供数据
  return house;
}

/**
 * 创建内容
 */
export const createCategory = async (post: categoryModal) => {
  // 准备查询
  const statement = `
    INSERT INTO categorys
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * 更新内容
 */
export const updateCategory = async (post: categoryModal, postId: number) => {
  // 准备查询
  const statement = `
    UPDATE categorys
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
}

/**
 * 删除内容
 */
export const deleteCategory = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM categorys
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 状态
 */
export const categoryStatusChange = async (postId: number, post: any) => {
  // 准备数据
  const statement = `
    UPDATE categorys
      SET ?
      WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);
  if ((data as any).affectedRows > 0) {
    await cornerStatusChangeByType(postId, 3, post.status);
  }

  // 提供数据
  return data;
};

/**
 * 存储文件信息
 */
export const createFile = async (
  file: categoryModal
) => {
  // 准备查询
  const statement = `
    INSERT INTO resource
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, file);

  // 提供数据
  return data;
}

/**
 * 更新文件信息
 */
export const updateFile = async (post: categoryModal, postId: number) => {
  const statement = `
    UPDATE resource
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
}

/**
 * 按 ID 查找文件
 */
export const findFileById = async (fileId: number, targetId: number, type: number) => {
  // 准备查询
  let sql = 'SELECT * FROM resource ';
  let and = false;
  if (fileId > 0) {
    sql += " where id = " + fileId;
    and = true;
  }
  if (targetId > 0) {
    sql += (and ? " and " : " where ") + " target_id = " + targetId;
    and = true;
  }
  if (type > 0) {
    sql += (and ? " and " : " where ") + " target_type = " + type;
  }

  // 执行查询
  const [data] = await connection.promise().query(sql);
  for (let item of data as any) {
    item.fullUrl = APP_URL + item.url + item.filename
  }
  // 提供数据
  return data as any;
}

/**
 * 删除内容文件
 */
export const deleteFileById = async (file: categoryModal) => {
  if (!file.id || file.id <= 0) return
  const statement = `delete from resource where id = ?`;
  const [data] = await connection.promise().query(statement, file.id);
  if ((data as any).affectedRows > 0) {
    let path = STATIC_PATH + file.url + file.filename

    try {
      fs.unlinkSync(path);
    } catch (error) {
      console.log("fileDeleteErr:", error);
    }
  };

}









// 获取电影类目
export const getMovieCategory = async () => {
  // 准备查询
  const statement = `
    SELECT * FROM categorys
    ORDER by id ASC
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement)

  // 提供数据
  return data;
}

// 获取电影类目 (admin)
export const getMovieCategoryByAdmin = async (pageNo: number, pageSize: number, status: any) => {
  // 准备查询
  let statement = `
    SELECT * FROM categorys WHERE status = ${status}
  `;

  // 执行查询
  statement += ` ORDER by id ASC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize;

  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalStatement = `SELECT COUNT(id) as total FROM categorys`
  const [result] = await connection.promise().query(totalStatement)
  let totalCount = result[0].total
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize;

  for (let item of data as any) {
    let count = await computeCategory(item.id);
    item.count = count.category_count;

    let { comment_count } = await commentTotal(item.id); // 统计该类目的评论
    item.comment_total = comment_count;

    let { like_count } = await categoryTotal(item.id); // 统计该类目的收藏
    item.categoryLikeTotal = like_count;
  }

  let res = {
    data,
    totalPage,
    totalCount
  }
  // 提供数据
  return res;
}


// 获取电影语言
export const getMovieLanguageCon = async () => {
  // 准备查询
  const statement = `
    SELECT * FROM language_con
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement)

  // 提供数据
  return data;
}

// 按 ID 获取电影类目
export const geByIdtMovieCategory = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM categorys
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId)

  // 提供数据
  return data[0];
}

// 获取电影制作国家
export const getMovieCountry = async () => {
  // 准备查询
  const statement = `
    SELECT * FROM country
    ORDER by id ASC
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement)

  // 提供数据
  return data;
}

// 获取电影制作国家 (admin)
export const getCountryByAdmin = async (pageNo: number, pageSize: number) => {
  // 准备查询
  let statement = `
    SELECT * FROM country
  `;

  // 执行查询
  statement += ` ORDER by id DESC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize

  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalStatement = `SELECT COUNT(id) as total FROM country`
  const [result] = await connection.promise().query(totalStatement)
  let totalCount = result[0].total
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize

  let res = {
    data,
    totalPage,
    totalCount
  }
  // 提供数据
  return res;
}

/**
 * 删除国家 (admin)
 */
export const deleteCountry = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM country
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 创建国家 (admin)
 */
export const createCountry = async (post: any) => {
  // 准备查询
  const statement = `
    INSERT INTO country
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * 更新国家 (admin)
 */
export const updateCountry = async (post: any, postId: number) => {
  // 准备查询
  const statement = `
    UPDATE country
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
}

/**
 * 按 ID 获取参与国家
 */
export const getIdCountry = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM country
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data[0];
}

/**
 * 统计类目评论
 */
const commentTotal = async (bookId: number) => {
  // 准备查询
  const statement = `
    SELECT COUNT(*) AS comment_count FROM comment WHERE category_id = ?;
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, bookId);

  // 提供数据
  return data[0];
}

/**
 * 统计类目
 */
const categoryTotal = async (bookId: number) => {
  // 准备查询
  const statement = `
    SELECT COUNT(*) AS like_count FROM start_book WHERE category_id = ?;
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, bookId);

  // 提供数据
  return data[0];
}